package com.baoyouqun.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baoyouqun.entity.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface UserMapper extends BaseMapper<User> {
    /**
     * 条件：city_code 为 null 或 空字符串
     */
    @Select("SELECT COUNT(*) FROM user WHERE city_code IS NULL OR city_code = ''")
    Long selectUnboundAreaUserCount();

    /**
     * 查询未绑定地区（cityCode为空）的VIP用户数
     * 条件：city_code 为 null 或 空字符串，且是VIP用户（is_vip=1）
     */
    @Select("SELECT COUNT(DISTINCT u.id) " +
            "FROM user u " +
            "INNER JOIN user_vip_time vip ON u.id = vip.user_id " +
            "WHERE " +
            "   (u.city_code IS NULL OR u.city_code = '') " +  // 未绑定地区
            "   AND vip.start_time <= NOW() " +               // VIP 已开始
            "   AND vip.end_time > NOW()")
    // VIP 未过期
    Long selectUnboundAreaValidVipCount();

    /**
     * 统计所有用户的地区分布（返回多条记录的List）
     *
     * @return List<Map>，每个Map包含两个键："cityCode"（地区编码）和"count"（用户数）
     */
    @Select("SELECT " +
            "  city_code AS cityCode,  " +  // 明确别名，避免使用关键字"key"
            "  COUNT(*) AS count       " +  // 计数别名
            "FROM user " +
            "WHERE city_code IS NOT NULL " +
            "GROUP BY city_code")
    List<Map<String, Object>> countByAreaCodes();  // 返回List接收多条记录


    /**
     * 2. 批量查询地区的豹友数（支持省/县市编码列表）
     *
     * @param areaCodes 地区编码列表（省编码/县市编码）
     * @param level     层级：PROVINCE=省（查User表province字段），CITY=县市（查User表city字段）
     * @return key:地区编码, value:豹友数
     */
    @Select("""
                <script>
                SELECT 
                    <if test='level == "PROVINCE"'>province</if>
                    <if test='level == "CITY"'>city</if> AS areaCode,
                    COUNT(id) AS userCount 
                FROM user 
                WHERE back_worker = 0 AND logout = 0 AND dis_enable = 0 
                AND 
                    <if test='level == "PROVINCE"'>province</if>
                    <if test='level == "CITY"'>city</if> IN 
                    <foreach collection="areaCodes" item="code" open="(" separator="," close=")">
                        #{code}
                    </foreach>
                GROUP BY 
                    <if test='level == "PROVINCE"'>province</if>
                    <if test='level == "CITY"'>city</if>
                </script>
            """)
    @MapKey("areaCode")
    Map<String, Long> batchSelectUserCountByAreaCodes(
            @Param("areaCodes") List<String> areaCodes,
            @Param("level") String level
    );

    /**
     * 统计符合条件的豹友总数（用于计算占比）
     */
    @Select("""
                    <script>
                SELECT COUNT(id) 
                FROM user 
                WHERE back_worker = 0 AND logout = 0 AND dis_enable = 0 
                <if test='provinceCode != null and provinceCode != ""'>AND province = #{provinceCode}</if> 
                <if test='cityCode != null and cityCode != ""'>AND city = #{cityCode}</if>
                    </script>
            """)
    Long selectLeopardTotalCount(
            @Param("provinceCode") String provinceCode,
            @Param("cityCode") String cityCode
    );


    /**
     * 查询全国总豹友数（所有非后台、未注销、未禁用用户）
     */
    @Select("SELECT COUNT(id) FROM user WHERE back_worker = 0 AND logout = 0 AND dis_enable = 0")
    Long selectNationwideTotalUserCount();

    /**
     * 批量查询多个县市编码的豹友数总和（用于省份汇总）
     *
     * @param districtCodes 县市编码列表
     * @return 总用户数
     */
    @Select("""
                <script>
                SELECT COUNT(id) AS totalUserCount
                FROM user 
                WHERE back_worker = 0 AND logout = 0 AND dis_enable = 0 
                AND city_code IN 
                <foreach collection="districtCodes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                </script>
            """)
    Long selectTotalUserCountByDistrictCodes(@Param("districtCodes") List<String> districtCodes);

    /**
     * 批量查询单个县市编码的豹友数（用于县市单独统计）
     *
     * @param districtCodes 县市编码列表
     * @return key:县市编码, value:用户数
     */
    @Select("""
                <script>
                SELECT city_code AS areaCode, COUNT(id) AS userCount 
                FROM user 
                WHERE back_worker = 0 AND logout = 0 AND dis_enable = 0 
                AND city_code IN 
                <foreach collection="districtCodes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                GROUP BY city_code
                </script>
            """)
    Map<String, Long> batchSelectUserCountByDistrictCodes(@Param("districtCodes") List<String> districtCodes);
}